package cn.turing.firecontrol.device.util;

import cn.turing.firecontrol.device.entity.DeviceIndoorRecordInspectionResults;
import cn.turing.firecontrol.device.entity.DeviceSensorDo;
import cn.turing.firecontrol.device.entity.Devicesenordo;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import java.io.File;
import java.io.FileInputStream;


/**
 * @auther Wyt
 * @date 2023/4/7/007 10:04
 */
public class PoiExcelUtil {
    /**
     * 导出数据到Excel
     *
     * @param response
     * @param filePath
     * @param devices
     */
    public static void writeDevicesToExcel(HttpServletResponse response, String filePath, List<Devicesenordo> devices) throws Exception {
       /* // 创建工作簿对象
        SXSSFWorkbook workbook = new SXSSFWorkbook(100);
        // 设置每次写入内存的行数，默认为100
       // workbook.setRowAccessWindowSize(1000);

        // 创建工作表对象
        Sheet sheet = workbook.createSheet("Sheet1");

        // 写入表头
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("设备id");
        headerRow.createCell(1).setCellValue("建筑id");
        headerRow.createCell(2).setCellValue("栏目id");
        headerRow.createCell(3).setCellValue("传感器编号");
        headerRow.createCell(4).setCellValue("所属楼层");
        headerRow.createCell(5).setCellValue("所在位置");
        headerRow.createCell(7).setCellValue("设备类型");
        headerRow.createCell(6).setCellValue("厂商");
        headerRow.createCell(8).setCellValue("型号");

        // 写入数据
        int rowIndex = 1;
        for (DeviceSensorDo device : devices) {
            Row row = sheet.createRow(rowIndex++);
            row.createCell(0).setCellValue(device.getId());
            row.createCell(1).setCellValue(device.getBuildingId());
            row.createCell(2).setCellValue(device.getChannelId());
            row.createCell(3).setCellValue(device.getSensorNo());
            row.createCell(4).setCellValue(device.getFloor());
            row.createCell(5).setCellValue(device.getPositionDescription());
            row.createCell(6).setCellValue(device.getEquipmentType());
            row.createCell(7).setCellValue(device.getManufacturer());
            row.createCell(8).setCellValue(device.getModel());
        }
        // 保存工作簿到文件

        FileOutputStream outputStream = new FileOutputStream(filePath);
       *//* workbook.write(outputStream);
        workbook.dispose(); // 释放临时文件并关闭文件流
        outputStream.close();*/

        /*
         * 优化后的代码
         * @param response
         * @param filePath
         * @param devices
         */
//        DeviceIndoorRecordInspectionResults entity = null;
//
//        TrimUtil.trimObjectEscape(entity);

        SXSSFWorkbook wb = new SXSSFWorkbook(100);

        CellStyleUtil cellStyleUtil = new CellStyleUtil(wb);
        SXSSFSheet sheet=wb.createSheet("设备传感器表");
        Integer [] width = {4000,5500,5500,6500};
        String [] columnName = {"传感器编号","设备类型","所属楼层","所在位置"};
        //设置样式
        Font font = wb.createFont();
        font.setBold(true);  // 设置粗体
        font.setFontHeightInPoints((short) 12);  // 设置字体大小
        CellStyle headerCellStyle = wb.createCellStyle();
        headerCellStyle.setFont(font);
        // 设置列宽,第一列字段名称
        cellStyleUtil.initWidthName(sheet,width,columnName);
        SXSSFRow row = null;
        Devicesenordo temp = null;
        int count = 0;
        if(devices.size()>100000){
            count = 100000;
        }else {
            count = devices.size();
        }
        for(int i=0;i<count;i++){
            //根据
            temp = devices.get(i);
            //行数
            row = sheet.createRow(i + 1);
           //获取集合中的数据
            String [] values = { temp.getSensorNo()+"", temp.getEquipmentType()+"", temp.getFloor(), temp.getPositionDescription()+"",};
            cellStyleUtil.setRowContent(row,values);
        }
        //输出Excel文件
        FileOutputStream outputStream = new FileOutputStream(filePath);
        wb.write(outputStream);
         // 释放临时文件并关闭文件流
        outputStream.close();
    }
   /**
     *  从Excel导入数据到数据库中
     *

     */
    /*public void imp() throws IOException {
        File file = new File("devices.xlsx");
        FileInputStream inputStream = new FileInputStream(file);

        Workbook workbook = new SXSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);

        Iterator<Row> rowIterator = sheet.rowIterator();
        List<Map<String, String>> dataList = new ArrayList<>();

        // 读取Excel中的数据到内存中
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();

            Map<String, String> data = new HashMap<>();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                data.put(sheet.getRow(0).getCell(cell.getColumnIndex()).getStringCellValue(), cell.getStringCellValue());
            }

            dataList.add(data);
        }

        // TODO: 将数据保存到数据库中或者其他操作
        // ...

        workbook.close();
        inputStream.close();
    }*/

}
